use MicroStrategy_Views
go

select top 100 InvoicetoNumber, SoldtoNumber, ShiptoNumber, ParentNumber from Sales_Star_Customer
where InvoicetoNumber in (7659429,7659432,7660441,7660443,7660446,7660449,7660455,7660457,7660458,7660460,7660462,7660470,7660471,7660473,7660483,7661487,7661489,7661490,7661517,7661521,7661527,7661538,7661554,7661583,7661590,7663619,7663630,7663640,7663658,7663663,7663667,7663672,7663684,7663703,7663731,7665785) 
--select * from Sales_Star_Customer
--where ShiptoNumber in (7659429,7659432,7660441,7660443,7660446,7660449,7660455,7660457,7660458,7660460,7660462,7660470,7660471,7660473,7660483,7661487,7661489,7661490,7661517,7661521,7661527,7661538,7661554,7661583,7661590,7663619,7663630,7663640,7663658,7663663,7663667,7663672,7663684,7663703,7663731,7665785) 

use RD4_DWCR
select top 100 
	AddressNumber, RelatedAddresssNumber,
	case when RelatedAddressNumber1=AddressNumber then 0 else RelatedAddressNumber1 end as [Related1],
	case when RelatedAddressNumber2 = AddressNumber then 0 else RelatedAddressNumber2 end as [Related2], 
	case when RelatedAddressNumber3 = AddressNumber then 0 else RelatedAddressNumber3 end as [Related3], 
	case when RelatedAddressNumber4 = AddressNumber then 0 else RelatedAddressNumber4 end as [Related4], 
	case when RelatedAddressNumber5 = AddressNumber then 0 else RelatedAddressNumber5 end as [Related5],  
	AlphaName
from 
	JDEABAddressBookMaster
where 
	AddressNumber in (7659429,7659432,7660441,7660443,7660446,7660449,7660455,7660457,7660458,7660460,7660462,7660470,7660471,7660473,7660483,7661487,7661489,7661490,7661517,7661521,7661527,7661538,7661554,7661583,7661590,7663619,7663630,7663640,7663658,7663663,7663667,7663672,7663684,7663703,7663731,7665785) 

use RD4_DWCR
go
select AddressNumber, RelatedAddressNumber FROM JDEARCMbyLineofBusines
where 
	AddressNumber in (7659429,7659432,7660441,7660443,7660446,7660449,7660455,7660457,7660458,7660460,7660462,7660470,7660471,7660473,7660483,7661487,7661489,7661490,7661517,7661521,7661527,7661538,7661554,7661583,7661590,7663619,7663630,7663640,7663658,7663663,7663667,7663672,7663684,7663703,7663731,7665785) 



use RD4_DWCR
select top 100 
	CM.AddressNumber, 
	case 
	when CM.RelatedAddressNumber = 2 then AB.RelatedAddressNumber2 
	when CM.relatedAddressNumber = 7 then AB.RelatedAddressNumber1
	else 0
	end as [SHIPTO],
	BillingAddressType,
	case
	when BillingAddressType = 'X' then ab.AddressNumber as [SOLDTO], ab.AddressNumber as [SHIPTO]
	when BillingAddressType = 'S' then ab.RelatedAddressNumber2 as [SOLDTO], ab.AddressNumber as [SHIPTO]
	else  ab.AddressNumber as [SOLDTO], ab.AddressNumber as [SHIPTO]

	
	
from 
	JDEARCMbyLineofBusines as CM join JDEABAddressBookMaster as AB on CM.AddressNumber=AB.AddressNumber
where 
	CM.AddressNumber in (7659429,7659432,7660441,7660443,7660446,7660449,7660455,7660457,7660458,7660460,7660462,7660470,7660471,7660473,7660483,7661487,7661489,7661490,7661517,7661521,7661527,7661538,7661554,7661583,7661590,7663619,7663630,7663640,7663658,7663663,7663667,7663672,7663684,7663703,7663731,7665785) 

 